import os

from openpyxl.styles import Alignment, Side, PatternFill, Border, Font
from prettytable import PrettyTable
import datetime
import openpyxl
from dateutil import parser
from chinese_calendar import is_workday
import pandas as pd

IP_PMC = "192.168.1.101"  # PMC服务器IP地址

# 单元格填充颜色
fill_erp = PatternFill(patternType="solid", fgColor="F0FFFF")  # ERP默认表头颜色
fill_blue = PatternFill(patternType="solid", fgColor="95B3D7")
fill_orange = PatternFill(patternType="solid", fgColor="F4B084")
fill_yellow = PatternFill(patternType="solid", fgColor="FFFF00")
fill_red = PatternFill(patternType="solid", fgColor="DA9694")
fill_green = PatternFill(patternType="solid", fgColor="92D050")
fill_white = PatternFill(patternType="solid", fgColor="FFFFFF")

# 单元格格式
alignment_center = Alignment(horizontal='center', vertical='center', wrap_text=True)
alignment_left = Alignment(horizontal='left', vertical='center', wrap_text=True)
alignment_lcf = Alignment(horizontal='left', vertical='center', wrap_text=False)
alignment_right = Alignment(horizontal='right', vertical='center', wrap_text=True)
alignment_erp_r = Alignment(horizontal='right', vertical='top', wrap_text=True)
alignment_erp_l = Alignment(horizontal='left', vertical='top', wrap_text=True)
alignment_wrap_text_F = Alignment(horizontal='left', vertical='top', wrap_text=False)

# 单元格边框
side_gary = Side(style="thin", color="A0A0A0")  # ERP导出默认灰色边框,A0A0A0
border_gray = Border(top=side_gary, bottom=side_gary, left=side_gary, right=side_gary)  # ERP导出默认灰色边框,A0A0A0
side_black = Side(style="thin", color="000000")  # 黑色边框

# 字体格式
font_ERP = Font(name="MS Sans Serif", color="000000", size=8)
font_ERP_9 = Font(name="MS Sans Serif", color="000000", size=9)  # ERP默认字体格式、通用物料判定表用，行高16，115%缩放
font_white = Font(color="FFFFFF")  # 白色字体
font_超链接 = Font(color="0000FF", underline="single")
font_red = Font(color="FF0000")  # 白色字体

# 时间
today = datetime.date.today()
today_str1: str = today.strftime("%Y.%m.%d")
# today_str1 = "2024.05.20"
today_str2 = today.strftime("%Y-%m-%d")
today_str3 = today.strftime("%Y/%m/%d")
today_str4 = today.strftime("%Y%m%d")

a = 1
yesterday = today - datetime.timedelta(days=a)
while not is_workday(yesterday):
    a += 1
    yesterday = today - datetime.timedelta(days=a)

yesterday_str1 = yesterday.strftime("%Y.%m.%d")
# yesterday_str1 = "2024.05.17"
yesterday_str2 = yesterday.strftime("%Y/%m/%d")
yesterday_str3 = yesterday.strftime("%Y%m%d")
real_yesterday = datetime.date.today() - datetime.timedelta(days=1)

# 滚动月
str1 = (today + datetime.timedelta(days=30)).strftime("%Y.%m") + "-" + (today + datetime.timedelta(days=90)).strftime(
    "%Y.%m")
str2 = (today + datetime.timedelta(days=30)).strftime("%m") + "-" + (today + datetime.timedelta(days=90)).strftime("%m")

nowtime = datetime.datetime.now()

#  ************************************************路径信息************************************************
path = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/"

lj_info = path + today_str1 + "/信息汇总" + today_str1 + ".xlsx"

lj_wlxxb_原 = path + today_str1 + "/source_file_Ⅱ/" + "物料信息表" + today_str1 + ".xlsx"
lj_wlxxb_y = path + yesterday_str1 + "/物料信息表" + yesterday_str1 + ".xlsx"
lj_wlxxb = path + today_str1 + "/物料信息表" + today_str1 + ".xlsx"
try:
    wb_wlxxb = openpyxl.load_workbook(lj_wlxxb)
    ws_wlxxb = wb_wlxxb["物料信息表" + today_str1]
    table_array_wlxxb = list(ws_wlxxb['A2':'AH' + str(ws_wlxxb.max_row)])
except FileNotFoundError:
    pass
lj_xinxi = path + today_str1 + "/source_file_Ⅱ/XINXI.xlsx"
lj_tyl1 = path + today_str1 + "/source_file_Ⅱ/" + "通用物料判定表1.xlsx"
lj_tyl2 = path + today_str1 + "/source_file_Ⅱ/" + "通用物料判定表2.xlsx"
lj_tyl = path + today_str1 + "/通用物料判定表" + today_str1 + ".xlsx"
lj_zyl = path + today_str1 + "/专用物料信息表" + today_str1 + ".xlsx"
lj_bom = path + today_str1 + "/source_file_Ⅱ/" + "BOM.xlsx"
lj_bom_yestoday = path + yesterday_str1 + "/source_file_Ⅱ/BOM.xlsx"
lj_bom_cd = path + today_str1 + "/BOM变更明细.xlsx"
lj_last_in = path + today_str1 + "/最后请购.xlsx"
lj_last_out = path + today_str1 + "/最后出库.xlsx"
lj_config = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/Tools/PMCtools_Python_v2.0/dist/config.ini"
lj_1 = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具"
lj_qgmxb = path + today_str1 + "/请购明细表" + today_str1 + ".xlsx"
lj_qgmxb_y = path + today_str1 + "/source_file_Ⅱ/请购明细表" + today_str1 + ".xlsx"
lj_xl_hqfk = path + today_str1 + "/供应商货期反馈" + today_str1 + ".xlsx"
lj_xl_xlmx = path + today_str1 + "/线缆备货--货期一览表" + today_str1 + ".xlsx"
lj_xl_xlmx_tocg = path + today_str1 + "/线缆备货--货期一览表to采购" + today_str1 + ".xlsx"
lj_pjmx = path + today_str1 + "/镜头、光源、配件备货--货期一览表" + today_str1 + ".xlsx"
lj_pjmx_tocg = path + today_str1 + "/镜头、光源、配件备货--货期一览表to采购" + today_str1 + ".xlsx"

lj_expected_procurement = path + today_str1 + "/source_file_Ⅱ/线缆在途明细表" + today_str1 + ".xlsx"
lj_gdmx = path + today_str1 + "/source_file_Ⅱ/工单完工情况报表" + today_str1 + ".xlsx"
lj_cp = path + "config/xlmx_cp.xlsx"
lj_tdl_cp = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\1，替代料管理\趋势分析\tdl_cp.xlsx"
lj_kcmxz = path + today_str1 + "/库存明细账" + today_str1 + '.xlsx'
lj_test1 = r"C:\Users\10352\Desktop\1.xlsx"
lj_gdwcqk_y = path + today_str1 + "/source_file_Ⅱ/工单完工情况报表" + today_str1 + ".xlsx"
lj_source_code = r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\Tools\PMCtools_Python_v2.0"
lj_kcmxz_yesterday = path + yesterday_str1 + "/库存明细账" + yesterday_str1 + ".xlsx"
lj_kcmxz_today_y = path + today_str1 + "/source_file_Ⅱ/今日库存明细账" + today_str1 + '.xlsx'
lj_kcmxz_today = path + today_str1 + "/今日库存明细账" + today_str1 + '.xlsx'
lj_qlxxb = path + today_str1 + "/欠料信息表" + today_str1 + ".xlsx"
lj_qlxxb_原 = path + today_str1 + "/source_file_Ⅱ/欠料信息表" + today_str1 + ".xlsx"
lj_data = path + "config/data.xlsx"
lj_tdlmx1 = path + today_str1 + "/替代料变更明细表" + today_str1 + ".xlsx"
lj_CBOM_y = path + today_str1 + "/source_file_Ⅱ/" + today_str2 + "成品BOM信息表.xlsx"
lj_CBOM = path + today_str1 + "/" + today_str2 + "成品BOM信息表.xlsx"
lj_CBOM_新 = path + today_str1 + "/成品BOM信息表" + today_str1 + ".xlsx"
lj_JH0020 = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/" + today_str1 + "/source_file_Ⅱ/JH0020.xlsx"
lj_JH0022 = "//192.168.70.101/19计划管理部/01.计划/1，数据处理工具/file/" + today_str1 + "/source_file_Ⅱ/JH0022.xlsx"
lj_JH0020_1 = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/mc_file/" + today_str1 + "/JH0020.xlsx"
lj_mc = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/物控报表" + today_str1 + ".xlsx"
lj_mccr_yesterday = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/3，物料状态管理/物控报表" + yesterday_str1 + ".xlsx"
lj_msd = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\物料状态看板.xlsx"
lj_change_ms = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\物料状态变更台账.xlsx"
lj_msd_md = r"\\192.168.70.101\19计划管理部\01.计划\5，物控管理\3，物料状态管理\物料关键参数异动台账.xlsx"
lj_tdlmx_t = path + today_str1 + "/source_file_Ⅱ/取替代料明细表" + today_str1 + ".xlsx"
lj_tdlmx_y = path + yesterday_str1 + "/source_file_Ⅱ/取替代料明细表" + yesterday_str1 + ".xlsx"
lj_tdlkb = "//192.168.70.101/19计划管理部/01.计划/5，物控管理/1，替代料管理/file/" + today_str1 + "替代料管理看板.xlsx"
lj_测试1 = r"C:\Users\10352\Desktop\1.xlsx"
lj_temp = r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\temp.xlsx"


# wb = openpyxl.load_workbook(lj_bom)  # 配套使用
# ws_bom = wb.worksheets[0]
def if_in_BOM(ws_bom, sn):
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(i, 3).value == str(sn):
            return True
    return False


def vlookup(lookup_value, table_array, col_index_num):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return row[col_index_num - 1].value


def vlookup_two(lookup_value1, lookup_value2, num2, table_array, col_index_num):
    for row in table_array:
        if str(row[0].value) == str(lookup_value1) and str(row[num2 - 1].value) == str(lookup_value2):
            return row[col_index_num - 1].value
    return ""


def find_max_length(lst):
    a: int = 0
    max_len = len(lst[0])  # 初始化为第一个元素的长度
    for i in range(0, len(lst)):
        if len(lst[i]) > max_len:
            max_len = len(lst[i])
            a = i
    return lst[a]


def chengtao_sn(int_sn: int, ws_tyl, ws_tdl, date):
    list1 = []  # 品号
    list2 = []  # 库存成套数量
    list3 = []  # 库存+在途在检+已请未购成套数量
    list4 = []  # 品名
    list5 = []  # 规格
    list7 = []  # 单耗
    list8 = []  # 最小起订量
    type1: str
    type2: str
    for i in range(2, ws_tyl.max_row + 1):
        if int(ws_tyl.cell(i, 4).value) == int_sn \
                and (int(ws_tyl.cell(i, 1).value) < 101010020000
                     or 101020010000 < int(ws_tyl.cell(i, 1).value) < 101030010000
                     or 101210010000 < int(ws_tyl.cell(i, 1).value) < 101220010000
                     or 104010010000 < int(ws_tyl.cell(i, 1).value) < 104140010000
                     or 101030010000 < int(ws_tyl.cell(i, 1).value) < 101030020000):
            if 替代料可用检查(int_sn, ws_tyl.cell(i, 1).value, ws_tdl):
                list4.append(ws_tyl.cell(i, 2).value + "（替）")
            # else:
            #     if 101210010000 < ws_tyl.cell(i, 1).value < 101220010000 and ws_tyl.cell(i, 13).value is not None:
            #         list4.append(ws_tyl.cell(i, 2).value + "（" + str(ws_tyl.cell(i, 13).value) + "）")
            #     else:
            #         list4.append(ws_tyl.cell(i, 2).value)
            else:
                list4.append(ws_tyl.cell(i, 2).value)
            list1.append(ws_tyl.cell(i, 1).value)
            list5.append(ws_tyl.cell(i, 3).value)
            list7.append(ws_tyl.cell(i, 7).value)
            list8.append(ws_tyl.cell(i, 22).value)
            list2.append(int(ws_tyl.cell(i, 18).value / ws_tyl.cell(i, 7).value))
            list3.append(int((ws_tyl.cell(i, 18).value + ws_tyl.cell(i, 19).value + ws_tyl.cell(i, 21).value) /
                             ws_tyl.cell(i, 7).value))
            type1 = ws_tyl.cell(i, 5).value
            type2 = ws_tyl.cell(i, 6).value
    if not list2:
        return "系统已调为N"

    else:
        min_kc = int(min(list2))
        min_kkc = int(min(list3))

    list2[list2.index(min(list2))] = "**" + str(list2[list2.index(min(list2))]) + "**"
    list3[list3.index(min(list3))] = "**" + str(list3[list3.index(min(list3))]) + "**"

    table: PrettyTable = PrettyTable()
    table.add_column("品号", list1)
    table.add_column("品名", list4)
    table.add_column("规格", list5)
    table.add_column("单耗", list7)
    table.add_column("库存成套数量", list2)
    table.add_column("备货成套数量", list3)
    table.add_column("最小起订量", list8)
    table.border = True
    table.header = True
    table.align = "c"
    # noinspection PyUnboundLocalVariable
    table.title = str(int_sn) + " " + type1 + " " + type2

    return "'" + str(table) + '\n' + "库存最低成套数量：" + str(min_kc) + '\n' + "备货最低成套数量：" + str(
        min_kkc) + '\n' + "物料信息结存日期：" + str(date)


def bom_start_1(ws_bom):
    # wb_bom = openpyxl.load_workbook(lj_bom)
    # ws_bom = wb_bom.worksheets[0]
    set1 = set()
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(i, 1).value[0] == "1":
            set1.add(ws_bom.cell(i, 1).value)
    return set1


def bom_1(sn: str, ws_bom):  # BOM表导出
    list_start_1 = bom_start_1(ws_bom)
    list1 = []
    list2 = []
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(i, 1).value == sn:
            list1.append(ws_bom.cell(i, 3).value)
            list2.append(ws_bom.cell(i, 4).value)
    list3 = []
    for i in list1:  # i为半成品品号
        if i[0] == "2" or i in list_start_1:
            list3.append(i)
            type_data = False
            for j in range(2, ws_bom.max_row + 1):  # j 为循环行数
                if ws_bom.cell(j, 1).value == i:
                    type_data = True
                    if ws_bom.cell(j, 3).value in list1:
                        list2[list1.index(ws_bom.cell(j, 3).value)] += (ws_bom.cell(j, 4).value * list2[list1.index(i)])
                    else:
                        list1.append(ws_bom.cell(j, 3).value)
                        list2.append(ws_bom.cell(j, 4).value * list2[list1.index(i)])
            if not type_data:
                print(i, "半成品无BOM！")
    for j in list3:
        list2.pop(list1.index(j))
        list1.remove(j)
    return list1, list2, list3  # 品号，用量


def bom_1_erp(ws_erpbom):  # ERP补充BOM
    list1_1 = []
    list1_2 = []
    list1_3 = []
    list1_4 = []
    for i in range(3, ws_erpbom.max_row + 1):
        if ws_erpbom.cell(i, 2).value in list1_1:
            list1_2[list1_1.index(ws_erpbom.cell(i, 2).value)] += ws_erpbom.cell(i, 12).value
        else:
            list1_1.append(ws_erpbom.cell(i, 2).value)
            list1_2.append(ws_erpbom.cell(i, 12).value)
            list1_3.append(ws_erpbom.cell(i, 4).value)
            list1_4.append(ws_erpbom.cell(i, 5).value)
    return [list1_1, list1_2, list1_3, list1_4]


# noinspection PyBroadException
def detect_date_str(text):  # 日期识别
    try:
        date = parser.parse(text)
        return date.strftime('%Y/%m/%d')
    except:
        return "异常日期"


# noinspection PyBroadException
def detect_date_int(int1):  # 日期识别
    try:
        return datetime.datetime.fromordinal(int1 + datetime.datetime(1900, 1, 1).toordinal() - 2).strftime("%Y/%m/%d")
    except:
        return "异常日期"


# noinspection PyBroadException
def detect_date(data, type_data):
    """
    1、type_data =  datetime.datetime
    2、type_data = str:%Y/%m/%d
    3、type_data =  datetime.date

    :param data: 输入任意格式日期
    :param type_data: 可选的日期格式
    :return: 标准格式日期
    """
    if type_data == 1:
        try:
            return parser.parse(data)
        except:
            return data
    elif type_data == 2:
        try:
            date = parser.parse(data)
            return date.strftime('%Y/%m/%d')
        except:
            return data
    elif type_data == 3:
        try:
            return parser.parse(data).date()
        except:
            return data
    elif type_data == 4:
        try:
            date = parser.parse(data)
            return date.strftime('%Y.%m.%d')
        except:
            return data


def erp_fc_1(cell):
    cell.font = font_ERP_9
    cell.alignment = alignment_erp_l
    cell.border = border_gray
    cell.fill = fill_erp


def erp_fc_2(cell):
    cell.font = font_ERP_9
    cell.alignment = alignment_erp_r
    cell.border = border_gray


def vlookup_pb(lookup_value, table_array):
    for row in table_array:
        if str(row[0].value) == str(lookup_value):
            return row


# noinspection PyShadowingNames
def str_pb(result: list):
    str2 = "拼版：" + str(result[8].value) + "\n"
    for m in range(3, 8):
        if result[m].value is not None:
            str2 += result[m].value + "\n"
    return str2


def Y状态BOM尾阶用量_pro():
    wb_bom = openpyxl.load_workbook(lj_bom)
    ws_bom = wb_bom.worksheets[0]
    dict_bom_尾阶 = {}
    for i in range(2, ws_bom.max_row + 1):
        if ws_bom.cell(row=i, column=1).value not in dict_bom_尾阶:
            dict_bom_尾阶[ws_bom.cell(row=i, column=1).value] = {}
            if ws_bom.cell(row=i, column=3).value in dict_bom_尾阶[ws_bom.cell(row=i, column=1).value]:
                dict_bom_尾阶[ws_bom.cell(row=i, column=1).value][ws_bom.cell(row=i, column=3).value] += ws_bom.cell(
                    row=i,
                    column=4).value
            else:
                dict_bom_尾阶[ws_bom.cell(row=i, column=1).value][ws_bom.cell(row=i, column=3).value] = ws_bom.cell(
                    row=i,
                    column=4).value
        else:
            if ws_bom.cell(row=i, column=3).value in dict_bom_尾阶[ws_bom.cell(row=i, column=1).value]:
                dict_bom_尾阶[ws_bom.cell(row=i, column=1).value][ws_bom.cell(row=i, column=3).value] += ws_bom.cell(
                    row=i,
                    column=4).value
            else:
                dict_bom_尾阶[ws_bom.cell(row=i, column=1).value][ws_bom.cell(row=i, column=3).value] = ws_bom.cell(
                    row=i,
                    column=4).value
    dict_bom_单阶 = dict_bom_尾阶.copy()

    for i in list(dict_bom_尾阶.keys()):
        for j in list(dict_bom_尾阶[i].keys()):
            if j in dict_bom_尾阶:
                for k in dict_bom_尾阶[j]:  # 处理嵌套BOM
                    if k in dict_bom_尾阶[i]:
                        dict_bom_尾阶[i][k] = dict_bom_尾阶[i][k] + dict_bom_尾阶[j][k] * dict_bom_尾阶[i][j]
                    else:
                        dict_bom_尾阶[i][k] = dict_bom_尾阶[j][k] * dict_bom_尾阶[i][j]
                del dict_bom_尾阶[i][j]

    dict_bom_规格 = {}
    for i in range(2, ws_bom.max_row + 1):
        if ((ws_bom.cell(row=i, column=1).value[0] == "3" or
             ws_bom.cell(row=i, column=1).value[0] == "5") and
                ws_bom.cell(row=i, column=2).value not in dict_bom_规格):
            dict_bom_规格[ws_bom.cell(row=i, column=2).value] = [[ws_bom.cell(row=i, column=1).value],  # 品号
                                                                 [ws_bom.cell(row=i, column=5).value if ws_bom.cell(
                                                                     row=i, column=5).value else ""],  # 品号描述信息
                                                                 [dict_bom_尾阶[ws_bom.cell(row=i, column=1).value]]
                                                                 # 品号bom
                                                                 ]
        elif (ws_bom.cell(row=i, column=2).value in dict_bom_规格 and
              ws_bom.cell(row=i, column=1).value not in dict_bom_规格[ws_bom.cell(row=i, column=2).value][0]):
            dict_bom_规格[ws_bom.cell(row=i, column=2).value][0].append(ws_bom.cell(row=i, column=1).value)
            if ws_bom.cell(row=i, column=5).value:
                dict_bom_规格[ws_bom.cell(row=i, column=2).value][1].append(ws_bom.cell(row=i, column=5).value)
            else:
                dict_bom_规格[ws_bom.cell(row=i, column=2).value][1].append("")
            dict_bom_规格[ws_bom.cell(row=i, column=2).value][2].append(
                dict_bom_尾阶[ws_bom.cell(row=i, column=1).value])
    list_bom = list(dict_bom_规格.keys())
    list_bom.sort()
    temp = dict_bom_规格
    dict_bom_规格 = {}
    for i in list_bom:
        dict_bom_规格[i] = temp[i]

    return [dict_bom_规格,  # 规格主键
            dict_bom_尾阶,  # 尾阶展开
            dict_bom_单阶,  # 单阶展开
            ]


def 路径内全部文件(directory):
    list_路径列表 = []
    for root, dirs, files in os.walk(directory):
        for file in files:
            list_路径列表.append(os.path.join(root, file).replace("\\", "/"))
    return list_路径列表


def 替代料可用检查(sn_成品, sn_替代料, ws_tdl):
    anser = False
    for i in range(2, ws_tdl.max_row + 1):
        if (ws_tdl.cell(row=i, column=1).value == str(sn_替代料)
                and ("通用替代" in ws_tdl.cell(i, 5).value or str(sn_成品) in ws_tdl.cell(i, 5).value)):
            if ws_tdl.cell(row=i, column=9).value != "冻结":
                anser = True
            else:
                if (ws_tdl.cell(i, 10).value
                    + ws_tdl.cell(i, 10).value
                    + ws_tdl.cell(i, 10).value
                    + ws_tdl.cell(i, 10).value) > 0:
                    anser = True
    return anser


# def 物控信息拉取_csv数据库版(sn_成品: str, df):
#     # 读取csv数据库
#     filtered_df = df[df['品号'] == sn_成品]
#
#     str_物控信息 = ""
#     for i in range(len(filtered_df)):
#         str_物控信息 += filtered_df.iloc[i]['OA流水号'] + "-" + filtered_df.iloc[i]['日期时间'] + "：" + \
#                         filtered_df.iloc[i]['备注一'] + "\n"
#     return str_物控信息

def 物控信息拉取_csv数据库版(sn_成品, df):
    # 读取csv数据库并过滤
    filtered_df = df[df['品号'] == sn_成品]

    # 检查是否有数据
    if filtered_df.empty:
        return None

    # 使用列表推导式提高效率，并处理空值和非字符串类型
    temp1 = 0
    str_物控信息 = ""
    for index, row in filtered_df.iterrows():
        temp1 += 1
        str_物控信息 += str(temp1) + "、" + str(row['OA流水号']) + "_" + str(row['日期时间']) + "：" + str(
            row['备注一']) + "\n"

    return str_物控信息[:-1]




def 物控信息拉取_sqlite数据库版(sn_成品, cursor):
    # 连接到SQLite数据库
    # conn = sqlite3.connect(r"\\192.168.70.101\19计划管理部\01.计划\1，数据处理工具\file\config\IK-PMC-MC.db")
    # cursor = conn.cursor()

    # 查询数据库并过滤
    # cursor.execute("SELECT  记录日期,来源, 备注 FROM 物控信息 WHERE 品号 = ?", (sn_成品,))
    # rows = cursor.fetchall()
    cursor.execute("SELECT 记录日期, 来源, 备注 FROM 物控信息 WHERE 品号 = ? ORDER BY 记录日期", (sn_成品,))
    rows = cursor.fetchall()

    cursor.execute("SELECT  通用信息 FROM 物控信息1 WHERE 品号 = ?", (sn_成品,))
    rows1 = cursor.fetchone()

    str_物控信息 = ""
    if rows1 is not None and rows1[0] is not None and rows1[0] != "":

        str_物控信息 = "通用信息：\n" + rows1[0] + "\n"
        if rows:
            str_物控信息 += "节点信息：\n"

    elif rows:
        str_物控信息 += "节点信息：\n"
    else:
        return None

    # 构造物控信息字符串
    for temp1, row in enumerate(rows, start=1):
        str_物控信息 += str(temp1) + "、" + str(row[0]) + "||" + str(row[1]) + "：" + str(row[2]) + "\n"

    # 关闭数据库连接
    # conn.close()

    return str_物控信息[:-1]
